/*
 * Group:PrePool K9-357 
 * 8/2010
 */
package dao;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;

import org.apache.log4j.Logger;

import util.Controller;

/**
 *
 */
public class UsersDAO {
	public static Logger LOG = Logger.getLogger("dao.UsersDAO");

	/**
	 * check if user is valid
	 * 
	 * @param username
	 *            user's name
	 * @param password
	 *            user's password
	 * @return a integer that is user's role
	 */
	public int isValid(String username, String password) {
		int userRole = -1; // this user account is not valid
		Connection cnn = null;
		PreparedStatement pstat = null;
		ResultSet rs = null;

		try {
			String strQuery = "select m_role from m_users where m_username=N'"
					+ username + "' and m_password=N'" + password + "'";
			cnn = Controller.getConnection();
			pstat = cnn.prepareStatement(strQuery);
			rs = pstat.executeQuery();
			if (rs.next()) {
				userRole = rs.getInt("m_role"); // 1: administrator, 2:
				// dictionary manager
			}
		} catch (Exception ex) {
			// LOG.error("isValid", ex);
		} finally {
			try {
				if (rs != null) {
					rs.close();
				}
				if (pstat != null) {
					pstat.close();
				}
				if (cnn != null) {
					cnn.close();
				}
			} catch (Exception ex) {
			}
		}
		return userRole;
	}

	/**
	 * change password
	 * 
	 * @param username
	 *            user's name
	 * @param password
	 *            user's old password
	 * @param newPassword
	 *            user's new password
	 * @return true if successful,false if fail
	 */
	public boolean changePassword(String username, String password,
			String newPassword) {
		boolean bOK = false;
		Connection cnn = null;
		CallableStatement pstat = null;

		try {
			cnn = Controller.getConnection();
			pstat = cnn.prepareCall("{call update_users_password(?,?,?)}");
			pstat.setString(1, username);
			pstat.setString(2, password);
			pstat.setString(3, newPassword);
			bOK = (pstat.executeUpdate() > 0);
		} catch (Exception ex) {
			// LOG.error("changePassword", ex);
		} finally {
			try {
				if (pstat != null) {
					pstat.close();
				}
				if (cnn != null) {
					cnn.close();
				}
			} catch (Exception ex2) {
			}
		}
		return bOK;
	}

	/**
	 * log user in and update m_date in m_users table
	 * 
	 * @param username
	 *            user's name
	 * @param password
	 *            user's password
	 * @return a integer that is user's role
	 */
	public int login(String username, String password) {
		int userRole = -1; // this user account is not valid
		Connection cnn = null;
		CallableStatement pstat = null;
		ResultSet rs = null;

		try {
			String strQuery = "select m_role,m_id from m_users where m_username=N'"
					+ username + "' and m_password=N'" + password + "'";
			cnn = Controller.getConnection();
			pstat = cnn.prepareCall(strQuery);
			rs = pstat.executeQuery();

			if (rs.next()) {
				userRole = rs.getInt("m_role"); // 1: administrator, 2:
				// dictionary manager
				int id = rs.getInt("m_id");
				rs.close();
				pstat.close();
				pstat = cnn.prepareCall("{call update_users_date(?)}");
				pstat.setInt(1, id);
				pstat.executeUpdate();
			}
		} catch (Exception ex) {
			// LOG.error("login", ex);
		} finally {
			try {
				if (rs != null) {
					rs.close();
				}
				if (pstat != null) {
					pstat.close();
				}
				if (cnn != null) {
					cnn.close();
				}
			} catch (Exception ex) {
			}
		}
		return userRole;
	}

	/**
	 * Delete a user
	 * 
	 * @param username
	 *            user's name
	 * @return true if successful,false if not
	 */
	public boolean deleteUser(String username) {
		Connection cnn = null;
		CallableStatement pstat = null;
		ResultSet rs = null;
		boolean bOK = false;

		try {
			cnn = Controller.getConnection();
			pstat = cnn
					.prepareCall("select m_id from m_users where m_username=N'"
							+ username + "'");
			rs = pstat.executeQuery();

			if (rs.next()) { // if user exists
				int userID = rs.getInt("m_id");
				rs.close();
				pstat.close();
				// delete from m_usrs table
				pstat = cnn.prepareCall("call m_users_delete(?)");
				pstat.setInt(1, userID);
				pstat.executeUpdate();
				pstat.close();
				bOK = true;
			}
		} catch (Exception ex) {
			// LOG.error("deleteUser", ex);
		} finally {
			try {
				if (rs != null) {
					rs.close();
				}
				if (pstat != null) {
					pstat.close();
				}
				if (cnn != null) {
					cnn.close();
				}
			} catch (Exception ex) {
			}
		}
		return bOK;
	}

	/**
	 * add a new user
	 * 
	 * @param username
	 *            user's name
	 * @param password
	 *            user's password
	 * @param role
	 *            user's role
	 * @return true if successful,false if not
	 */
	public boolean addUser(String username, String password, int role) {
		boolean bOK = false;
		Connection cnn = null;
		CallableStatement cstat = null;
		ResultSet rs = null;

		try {
			cnn = Controller.getConnection();
			cstat = cnn.prepareCall("select *from m_users where m_username=N'"
					+ username + "'");
			rs = cstat.executeQuery();

			if (rs.next() == false) {
				rs.close();
				cstat.close();
				cstat = cnn.prepareCall("{call m_users_insert(?, ?, ?)}");
				cstat.setString(1, username);
				cstat.setString(2, password);
				cstat.setInt(3, role);
				bOK = (cstat.executeUpdate() == 1);
			}
		} catch (Exception ex) {
			// LOG.error("addUser", ex);
		} finally {
			try {
				if (rs != null) {
					rs.close();
				}
				if (cstat != null) {
					cstat.close();
				}
				if (cnn != null) {
					cnn.close();
				}
			} catch (Exception ex) {
			}
		}
		return bOK;
	}

	/**
	 * Get user's Id by name
	 * 
	 * @param name
	 *            user's name
	 * @return user's Id
	 */
	public int getUserIDByName(String name) {
		Connection cnn = null;
		PreparedStatement pstat = null;
		ResultSet rs = null;
		int userId = -1;

		try {
			cnn = Controller.getConnection();
			pstat = cnn
					.prepareStatement("select m_id from m_users where m_username=N'"
							+ name + "'");
			rs = pstat.executeQuery();
			if (rs.next()) {
				userId = rs.getInt("m_id");
			}
		} catch (Exception ex) {
			// LOG.error("getUserIDByName", ex);
		} finally {
			try {
				if (rs != null) {
					rs.close();
				}
				if (pstat != null) {
					pstat.close();
				}
				if (cnn != null) {
					cnn.close();
				}
			} catch (Exception ex) {
			}
		}
		return userId;
	}

	/**
	 * get user's role by name
	 * 
	 * @param username
	 *            user's name
	 * @return user's role
	 */
	public int getRole(String username) {
		Connection cnn = null;
		PreparedStatement pstat = null;
		int role = 0;

		try {
			cnn = Controller.getConnection();
			pstat = cnn
					.prepareStatement("select m_role from m_users where m_username = ?");
			pstat.setString(1, username);
			ResultSet rs = pstat.executeQuery();
			rs.next();

			role = rs.getInt("m_role");
		}

		catch (Exception e) {
			// TODO: handle exception
		}
		return role;
	}
}
